--DB2

--SELECT SCHEMA  
SELECT NAME, CREATE_TIME AS CRTM, 1 AS INUSE  
	FROM SYSIBM.SYSSCHEMATA

--SELECT TABLE   
SELECT TRIM(CREATOR) || '.' || NAME AS NAME, 
	CTIME AS CRTM, 1 AS INUSE
FROM SYSIBM.SYSTABLES WHERE TYPE = 'T'

--SELECT TRIGGER 
SELECT NAME, 
	TRIM(TBCREATOR) || '.' || TBNAME AS OWTB,
	CAST(TEXT AS CLOB) AS SQL,
	CREATE_TIME AS CRTM,
	CASE VALID WHEN 'Y' THEN 1 ELSE 0 END  
FROM SYSIBM.SYSTRIGGERS

--SELECT VIEW
SELECT TRIM(CREATOR) || '.' || NAME AS  OWTB,
	CAST(TEXT AS CLOB) AS SQL,
	(CURRENT TIMESTAMP) AS CRTM,
	CASE VALID WHEN 'Y' THEN 1 ELSE 0 END  
FROM SYSIBM.SYSVIEWS

--SELECT VALIDATE  
SELECT COUNT(*) FROM SYSIBM.SYSTABLES

--DEPLOY TRIGGER DELETE
CREATE TRIGGER  DITS.DATA_MONITOR_DTG
            AFTER DELETE ON DITS.DATA_MONITOR_TB 
            REFERENCING OLD AS O  
            FOR EACH ROW MODE DB2SQL 
BEGIN ATOMIC 
	INSERT INTO DITS.DATA_MONITOR_LOG(NSM, CLTB, OPT, CLID, INUSE) 
		VALUES('DITS.NSM', 'DITS.DATA_MONITOR_TB', 3, TRIM(CAST(DATA_ID AS CHAR(128))), 1); 
END

--UNDEPLOY TRIGGER DELETE
DROP TRIGGER DITS.DATA_MONITOR_DTG

--DEPLOY TRIGGER INSERT
CREATE TRIGGER  DITS.DATA_MONITOR_ITG
			AFTER INSERT ON DITS.DATA_MONITOR_TB
            REFERENCING NEW AS N
            FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
	INSERT INTO DITS.DATA_MONITOR_LOG(NSM, CLTB, OPT, CLID, INUSE) 
		VALUES('DITS.NSM', 'DITS.DATA_MONITOR_TB', 1, TRIM(CAST(DATA_ID AS CHAR(128))), 1);
END

--UNDEPLOY TRIGGER INSERT
DROP TRIGGER DITS.DATA_MONITOR_ITG


--DEPLOY TRIGGER UPDATE
CREATE TRIGGER  DITS.DATA_MONITOR_UTG
            AFTER UPDATE ON DITS.DATA_MONITOR_TB
            REFERENCING OLD AS O NEW AS N
            FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
	DECLARE @OLDFIELDVALUE VARCHAR(512);
	DECLARE @NEWFIELDVALUE VARCHAR(512);
	SET @OLDFIELDVALUE = TRIM(CAST(O.DATA_ID AS CHAR(128)));
	SET @NEWFIELDVALUE = TRIM(CAST(N.DATA_ID AS CHAR(128)));
	IF O.DATA_ID = N.DATA_ID THEN
		INSERT INTO DITS.DATA_MONITOR_LOG(NSM, CLTB, OPT, CLID, INUSE)
			VALUES('DITS.NSM', 'DITS.DATA_MONITOR_TB', 2, @OLDFIELDVALUE, 1);
	ELSE
		INSERT INTO DITS.DATA_MONITOR_LOG(NSM, CLTB, OPT, CLID, INUSE)
			VALUES('DITS.NSM', 'DITS.DATA_MONITOR_TB', 3, @OLDFIELDVALUE, 1 );
		INSERT INTO DITS.DATA_MONITOR_LOG(NSM, CLTB, OPT, CLID, INUSE)
			VALUES('DITS.NSM', 'DITS.DATA_MONITOR_TB', 1, @NEWFIELDVALUE, 1 );
	END IF;
END

--UNDEPLOY TRIGGER UPDATE
DROP TRIGGER DITS.DATA_MONITOR_UTG


--DEPLOY TABLE
create table DITS.DATA_MONITOR_LOG(
	--自动主键
	SID          BIGINT                 not null generated by default as identity,
	--名字空间
	NSM       VARCHAR(42)    	not null,
	--变更的表名
	CLTB       VARCHAR(68)    	not null,
	--变更的操作,0-删除;1-插入; 2-更新
	OPT      	INT    					not null,
	--变更数据的主键内容
	CLID        VARCHAR(256)  not null,
	--是否可用,0-不可用;1-可用
	INUSE     SMALLINT          default 1,
	--操作时间
	OPTM     TIMESTAMP       default CURRENT TIMESTAMP,
	constraint "DITS.DATA_MONITOR_LOG.KEY" primary key (SID) 
)

--UNDEPLOY TABLE
drop table DITS.DATA_MONITOR_LOG
